Discount (or Premium) Bond Model (Excel XLSX)

This Bond Modeling and Amortization Schedule Template is designed for anyone looking to streamline the process of evaluating and tracking bond investments. Whether you are buying bonds at a discount or a premium, the template simplifies the complexities of interest recognition in accordance with IFRS 9 standards. By integrating seamlessly into a three-statement financial model, it provides a comprehensive view of how bond transactions impact the income statement, balance sheet, and cash flow statement. With customizable frequency options, zero-coupon bond calculations, and sensitivity tables, users can easily adapt the template to various scenarios and gain valuable insights into the performance and profitability of their fixed-income investments.

Key Features

Dynamic Amortization Schedules
Automatically generate period-by-period breakdowns of principal and interest for monthly, quarterly, biannual, or annual schedules.

Fully Integrated 3-Statement Model
Observe the direct impact of bond transactions on the income statement, balance sheet, and cash flow statement—from the perspectives of both purchaser and issuer.

IFRS 9 Compliance
Accurately calculate and allocate interest income or expense for bonds purchased at a discount or premium, ensuring compliance with relevant accounting standards.

Multiple Payment Structures
Handle both bullet (interest-only) and amortizing bond models to fit diverse financing structures and investment types.

Zero-Coupon Bonds
Choose between defining your own purchase price or auto-calculating it based on a specified interest rate for zero-coupon bonds.

Sensitivity Analysis
Leverage two built-in sensitivity tables to quickly assess the effects of varying coupon rates, purchase prices, effective interest rates, and loan terms.

Comprehensive Outputs
Evaluate key metrics such as total invested, total returned, total profit, ROI, and effective interest rate to support informed decision-making.

Customizable & User-Friendly
Use a fully unlocked Excel file with color-coded fields for easy data input and immediate, automated calculations.

Visual Enhancements
View results through two sets of charts, each optimized for scenarios with many or fewer periods, offering clear illustrations of financial performance.

Note, all inputs are clearly formatted with yellow cells and blue text.